﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using Npgsql;
//using SpectationClient.DataBaseDescription;
using SpectationClient.DataBaseDescription;
using SpectationClient.Stuff;

namespace SpectationClient.SQLCommandBuilder {
    public class InsertValueCollection {
        private TableInfo ti;
        private List<InsertValues> rows = new List<InsertValues>();
        private HashSet<String> columns = new HashSet<string>();

        public InsertValueCollection(ref TableInfo ti) {
            this.ti = ti;
        }

        public int Count {
            get { return rows.Count; }
        }


        /*
        public void AddIDs(String[] idColumns, List<Object[]> idValues) {
            if(idColumns.Count() != idValues.Count()) throw new Exception("number of id columns and id values differs.");
            InsertValues iv = new InsertValues(ref this.ti);
            for(int i=0; i < idColumns.Length; i++) {
                iv.Add(idColumns[i], idValues[i]);
            }
        }

        public void AddID(String[] idColumns, Object[] idValues) {
           if(idColumns.Count() != idValues.Count()) throw new Exception("number of id columns and id values differs.");
           InsertValues iv = new InsertValues(ref this.ti);
           for(int i=0; i < idColumns.Length; i++){
               iv.Add(idColumns[i], idValues[i]);
           }
        }*/
       

        public void Add(InsertValues rowValues) {
            if(rowValues.TableInfo != this.ti) {
                throw new Exception("wrong tableName info");
            }
            foreach(String column in rowValues.Values.Keys){
                this.columns.Add(column);
            }
            this.rows.Add(rowValues);
        }

        public NpgsqlCommand getInsertCmd() {
            return this.getInsertCmd(false);
        }

        public NpgsqlCommand getInsertCmd(bool returnPrimaryKeyValues) {
            return this.getInsertCmd(returnPrimaryKeyValues, null);
        }

        public NpgsqlCommand getInsertCmd(bool returnPrimaryKeyValues, NpgsqlConnection connection) {
            return this.getInsertCmd(returnPrimaryKeyValues, connection, null);
        }
        public NpgsqlCommand getInsertCmd(bool returnPrimaryKeyValues, NpgsqlConnection connection, NpgsqlTransaction transaction) {
            if(this.rows.Count == 0) return null;
            NpgsqlCommand cmd = new NpgsqlCommand(
                String.Format("INSERT INTO {0} ({1}) VALUES"
                , this.ti.SchemaTableName, TextHelper.combine(this.columns, ",")));
            cmd.Connection = connection;
            cmd.Transaction = transaction;
            
            int cntParameters = 0;
            int cntColumn = 0;
            int cntRows = 0;
            foreach(InsertValues rv in this.rows) {
                cmd.CommandText += "(";
                foreach(String column in this.columns) {
                    if(rv.Values.ContainsKey(column)) {
                        ColumnInfoGeometry gci = this.ti[column] as ColumnInfoGeometry;
                        ColumnInfo ci = this.ti[column] as ColumnInfo;
                        if(gci != null) {
                            cmd.CommandText += rv.Values[column];
                        } else {
                            String parameterName = String.Format("@p{0}", cntParameters);
                            cmd.CommandText += parameterName;
                            cmd.Parameters.AddWithValue(parameterName, rv.Values[column]);
                            cntParameters++;
                        }
                    } else {
                        cmd.CommandText += "DEFAULT";
                    }
                    if(cntColumn < this.columns.Count - 1) cmd.CommandText += ", ";
                    cntColumn++;
                }

                cmd.CommandText += ")";
                if(cntRows < this.rows.Count - 1) cmd.CommandText += ",";
                cntRows++;
                cntColumn = 0;
            }
            if(returnPrimaryKeyValues) {
                cmd.CommandText += String.Format(" RETURNING ({0})"
                    , TextHelper.combine(this.ti.PrimaryKey.Names, "\"{0}\"", ","));
            }
            return cmd;
        }

    }
}
